{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Chapter 8: Reshaping and Tidying Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Technical Requirements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;1948/49&quot;</td><td>25464</td><td>2.4034e6</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>&quot;1949/50&quot;</td><td>26433</td><td>2.445e6</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>&quot;1950/51&quot;</td><td>29813</td><td>2.281e6</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>&quot;1951/52&quot;</td><td>30462</td><td>2.102e6</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>&quot;1952/53&quot;</td><td>33675</td><td>2.134e6</td><td>null</td><td>null</td><td>null</td><td>null</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌─────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬──────┐\n",
       "│ year    ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt  │\n",
       "│ ---     ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---  │\n",
       "│ str     ┆ i64      ┆ f64         ┆ f64           ┆ f64      ┆ f64        ┆ f64  │\n",
       "╞═════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪══════╡\n",
       "│ 1948/49 ┆ 25464    ┆ 2.4034e6    ┆ null          ┆ null     ┆ null       ┆ null │\n",
       "│ 1949/50 ┆ 26433    ┆ 2.445e6     ┆ null          ┆ null     ┆ null       ┆ null │\n",
       "│ 1950/51 ┆ 29813    ┆ 2.281e6     ┆ null          ┆ null     ┆ null       ┆ null │\n",
       "│ 1951/52 ┆ 30462    ┆ 2.102e6     ┆ null          ┆ null     ┆ null       ┆ null │\n",
       "│ 1952/53 ┆ 33675    ┆ 2.134e6     ┆ null          ┆ null     ┆ null       ┆ null │\n",
       "└─────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴──────┘"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pl.read_csv('../data/academic.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from polars import selectors as cs\n",
    "df = (\n",
    "    df\n",
    "    .select(\n",
    "        pl.col('year').alias('academic_year'), \n",
    "        cs.numeric().cast(pl.Int64)\n",
    "    )\n",
    "    .filter(pl.col('academic_year').str.slice(0,4).cast(pl.Int32)>=2018)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Turning columns into rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>student_type</th><th>count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>&quot;students&quot;</td><td>1095299</td></tr><tr><td>&quot;2019/20&quot;</td><td>&quot;students&quot;</td><td>1075496</td></tr><tr><td>&quot;2020/21&quot;</td><td>&quot;students&quot;</td><td>914095</td></tr><tr><td>&quot;2021/22&quot;</td><td>&quot;students&quot;</td><td>948519</td></tr><tr><td>&quot;2022/23&quot;</td><td>&quot;students&quot;</td><td>1057188</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌───────────────┬──────────────┬─────────┐\n",
       "│ academic_year ┆ student_type ┆ count   │\n",
       "│ ---           ┆ ---          ┆ ---     │\n",
       "│ str           ┆ str          ┆ i64     │\n",
       "╞═══════════════╪══════════════╪═════════╡\n",
       "│ 2018/19       ┆ students     ┆ 1095299 │\n",
       "│ 2019/20       ┆ students     ┆ 1075496 │\n",
       "│ 2020/21       ┆ students     ┆ 914095  │\n",
       "│ 2021/22       ┆ students     ┆ 948519  │\n",
       "│ 2022/23       ┆ students     ┆ 1057188 │\n",
       "└───────────────┴──────────────┴─────────┘"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_df = df.unpivot(\n",
    "    index='academic_year', \n",
    "    on=[\n",
    "        'students',\n",
    "        'us_students',\n",
    "        'undergraduate',\n",
    "        'graduate',\n",
    "        'non_degree',\n",
    "        'opt'\n",
    "    ],\n",
    "    variable_name='student_type',\n",
    "    value_name='count'\n",
    ")\n",
    "long_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (6, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>student_type</th></tr><tr><td>str</td></tr></thead><tbody><tr><td>&quot;students&quot;</td></tr><tr><td>&quot;us_students&quot;</td></tr><tr><td>&quot;undergraduate&quot;</td></tr><tr><td>&quot;non_degree&quot;</td></tr><tr><td>&quot;graduate&quot;</td></tr><tr><td>&quot;opt&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (6, 1)\n",
       "┌───────────────┐\n",
       "│ student_type  │\n",
       "│ ---           │\n",
       "│ str           │\n",
       "╞═══════════════╡\n",
       "│ students      │\n",
       "│ us_students   │\n",
       "│ undergraduate │\n",
       "│ non_degree    │\n",
       "│ graduate      │\n",
       "│ opt           │\n",
       "└───────────────┘"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_df.select('student_type').unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>variable</th><th>value</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>&quot;students&quot;</td><td>1095299</td></tr><tr><td>&quot;2019/20&quot;</td><td>&quot;students&quot;</td><td>1075496</td></tr><tr><td>&quot;2020/21&quot;</td><td>&quot;students&quot;</td><td>914095</td></tr><tr><td>&quot;2021/22&quot;</td><td>&quot;students&quot;</td><td>948519</td></tr><tr><td>&quot;2022/23&quot;</td><td>&quot;students&quot;</td><td>1057188</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌───────────────┬──────────┬─────────┐\n",
       "│ academic_year ┆ variable ┆ value   │\n",
       "│ ---           ┆ ---      ┆ ---     │\n",
       "│ str           ┆ str      ┆ i64     │\n",
       "╞═══════════════╪══════════╪═════════╡\n",
       "│ 2018/19       ┆ students ┆ 1095299 │\n",
       "│ 2019/20       ┆ students ┆ 1075496 │\n",
       "│ 2020/21       ┆ students ┆ 914095  │\n",
       "│ 2021/22       ┆ students ┆ 948519  │\n",
       "│ 2022/23       ┆ students ┆ 1057188 │\n",
       "└───────────────┴──────────┴─────────┘"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.unpivot(\n",
    "    index='academic_year', \n",
    "    on=cs.numeric()\n",
    ").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "lf = df.lazy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>student_type</th><th>count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>&quot;students&quot;</td><td>1095299</td></tr><tr><td>&quot;2019/20&quot;</td><td>&quot;students&quot;</td><td>1075496</td></tr><tr><td>&quot;2020/21&quot;</td><td>&quot;students&quot;</td><td>914095</td></tr><tr><td>&quot;2021/22&quot;</td><td>&quot;students&quot;</td><td>948519</td></tr><tr><td>&quot;2022/23&quot;</td><td>&quot;students&quot;</td><td>1057188</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌───────────────┬──────────────┬─────────┐\n",
       "│ academic_year ┆ student_type ┆ count   │\n",
       "│ ---           ┆ ---          ┆ ---     │\n",
       "│ str           ┆ str          ┆ i64     │\n",
       "╞═══════════════╪══════════════╪═════════╡\n",
       "│ 2018/19       ┆ students     ┆ 1095299 │\n",
       "│ 2019/20       ┆ students     ┆ 1075496 │\n",
       "│ 2020/21       ┆ students     ┆ 914095  │\n",
       "│ 2021/22       ┆ students     ┆ 948519  │\n",
       "│ 2022/23       ┆ students     ┆ 1057188 │\n",
       "└───────────────┴──────────────┴─────────┘"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    lf\n",
    "    .unpivot(\n",
    "        index='academic_year', \n",
    "        on=cs.numeric(),\n",
    "        variable_name='student_type',\n",
    "        value_name='count'\n",
    "    )\n",
    "    .collect()\n",
    "    .head()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Turning rows into columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting ready"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "from polars import selectors as cs\n",
    "\n",
    "long_df = (\n",
    "    pl.read_csv('../data/academic.csv')\n",
    "    .select(\n",
    "        pl.col('year').alias('academic_year'), \n",
    "        cs.numeric().cast(pl.Int64)\n",
    "    )\n",
    "    .filter(\n",
    "        pl.col('academic_year').str.slice(0,4).cast(pl.Int32)>=2018\n",
    "    )\n",
    "    .unpivot(\n",
    "        index='academic_year', \n",
    "        on=[\n",
    "            'students',\n",
    "            'us_students',\n",
    "            'undergraduate',\n",
    "            'graduate',\n",
    "            'non_degree',\n",
    "            'opt'\n",
    "        ],\n",
    "        variable_name='student_type',\n",
    "        value_name='count'\n",
    "    \n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>student_type</th><th>count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>&quot;students&quot;</td><td>1095299</td></tr><tr><td>&quot;2019/20&quot;</td><td>&quot;students&quot;</td><td>1075496</td></tr><tr><td>&quot;2020/21&quot;</td><td>&quot;students&quot;</td><td>914095</td></tr><tr><td>&quot;2021/22&quot;</td><td>&quot;students&quot;</td><td>948519</td></tr><tr><td>&quot;2022/23&quot;</td><td>&quot;students&quot;</td><td>1057188</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌───────────────┬──────────────┬─────────┐\n",
       "│ academic_year ┆ student_type ┆ count   │\n",
       "│ ---           ┆ ---          ┆ ---     │\n",
       "│ str           ┆ str          ┆ i64     │\n",
       "╞═══════════════╪══════════════╪═════════╡\n",
       "│ 2018/19       ┆ students     ┆ 1095299 │\n",
       "│ 2019/20       ┆ students     ┆ 1075496 │\n",
       "│ 2020/21       ┆ students     ┆ 914095  │\n",
       "│ 2021/22       ┆ students     ┆ 948519  │\n",
       "│ 2022/23       ┆ students     ┆ 1057188 │\n",
       "└───────────────┴──────────────┴─────────┘"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    long_df\n",
    "    .pivot(\n",
    "        index='academic_year', \n",
    "        values='count', \n",
    "        on='student_type'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    long_df\n",
    "    .group_by('academic_year', maintain_order=True)\n",
    "    .agg(\n",
    "        pl.col('count').filter(pl.col('student_type')=='students').sum().alias('students'),\n",
    "        pl.col('count').filter(pl.col('student_type')=='us_students').sum().alias('us_students'),\n",
    "        pl.col('count').filter(pl.col('student_type')=='undergraduate').sum().alias('undergraduate'),\n",
    "        pl.col('count').filter(pl.col('student_type')=='graduate').sum().alias('graduate'),\n",
    "        pl.col('count').filter(pl.col('student_type')=='non_degree').sum().alias('non_degree'),\n",
    "        pl.col('count').filter(pl.col('student_type')=='opt').sum().alias('opt')\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>undergraduate</th><th>non_degree</th><th>us_students</th><th>opt</th><th>graduate</th><th>students</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>431930</td><td>62341</td><td>19828000</td><td>223085</td><td>377943</td><td>1095299</td></tr><tr><td>&quot;2019/20&quot;</td><td>419321</td><td>58201</td><td>19720000</td><td>223539</td><td>374435</td><td>1075496</td></tr><tr><td>&quot;2020/21&quot;</td><td>359787</td><td>21151</td><td>19744000</td><td>203885</td><td>329272</td><td>914095</td></tr><tr><td>&quot;2021/22&quot;</td><td>344532</td><td>34131</td><td>20327000</td><td>184759</td><td>385097</td><td>948519</td></tr><tr><td>&quot;2022/23&quot;</td><td>347602</td><td>43766</td><td>18961280</td><td>198793</td><td>467027</td><td>1057188</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬───────────────┬────────────┬─────────────┬────────┬──────────┬──────────┐\n",
       "│ academic_year ┆ undergraduate ┆ non_degree ┆ us_students ┆ opt    ┆ graduate ┆ students │\n",
       "│ ---           ┆ ---           ┆ ---        ┆ ---         ┆ ---    ┆ ---      ┆ ---      │\n",
       "│ str           ┆ i64           ┆ i64        ┆ i64         ┆ i64    ┆ i64      ┆ i64      │\n",
       "╞═══════════════╪═══════════════╪════════════╪═════════════╪════════╪══════════╪══════════╡\n",
       "│ 2018/19       ┆ 431930        ┆ 62341      ┆ 19828000    ┆ 223085 ┆ 377943   ┆ 1095299  │\n",
       "│ 2019/20       ┆ 419321        ┆ 58201      ┆ 19720000    ┆ 223539 ┆ 374435   ┆ 1075496  │\n",
       "│ 2020/21       ┆ 359787        ┆ 21151      ┆ 19744000    ┆ 203885 ┆ 329272   ┆ 914095   │\n",
       "│ 2021/22       ┆ 344532        ┆ 34131      ┆ 20327000    ┆ 184759 ┆ 385097   ┆ 948519   │\n",
       "│ 2022/23       ┆ 347602        ┆ 43766      ┆ 18961280    ┆ 198793 ┆ 467027   ┆ 1057188  │\n",
       "└───────────────┴───────────────┴────────────┴─────────────┴────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student_types = [ \n",
    "    col for col in long_df.select('student_type').unique().to_series().to_list() \n",
    "] \n",
    "\n",
    "agg_cols = [ \n",
    "    ( \n",
    "        pl.col('count') \n",
    "        .filter(pl.col('student_type')==stu_type) \n",
    "        .sum() \n",
    "        .alias(stu_type)  \n",
    "    ) \n",
    "    for stu_type in student_types \n",
    "] \n",
    "\n",
    "( \n",
    "    long_df \n",
    "    .group_by('academic_year', maintain_order=True) \n",
    "    .agg(agg_cols) \n",
    ") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>count_0</th><th>count_1</th><th>count_2</th><th>count_3</th><th>count_4</th><th>count_5</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 6)\n",
       "┌─────────┬──────────┬─────────┬─────────┬─────────┬─────────┐\n",
       "│ count_0 ┆ count_1  ┆ count_2 ┆ count_3 ┆ count_4 ┆ count_5 │\n",
       "│ ---     ┆ ---      ┆ ---     ┆ ---     ┆ ---     ┆ ---     │\n",
       "│ i64     ┆ i64      ┆ i64     ┆ i64     ┆ i64     ┆ i64     │\n",
       "╞═════════╪══════════╪═════════╪═════════╪═════════╪═════════╡\n",
       "│ 1095299 ┆ 19828000 ┆ 431930  ┆ 377943  ┆ 62341   ┆ 223085  │\n",
       "│ 1075496 ┆ 19720000 ┆ 419321  ┆ 374435  ┆ 58201   ┆ 223539  │\n",
       "│ 914095  ┆ 19744000 ┆ 359787  ┆ 329272  ┆ 21151   ┆ 203885  │\n",
       "│ 948519  ┆ 20327000 ┆ 344532  ┆ 385097  ┆ 34131   ┆ 184759  │\n",
       "│ 1057188 ┆ 18961280 ┆ 347602  ┆ 467027  ┆ 43766   ┆ 198793  │\n",
       "└─────────┴──────────┴─────────┴─────────┴─────────┴─────────┘"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_df.unstack(step=5, columns='count', how='vertical')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There is more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (6, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (6, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wide_df_with_dups = (\n",
    "    pl.concat([\n",
    "        df.head(1),\n",
    "        df\n",
    "    ])\n",
    ")\n",
    "\n",
    "wide_df_with_dups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "long_df_with_dups = (\n",
    "    wide_df_with_dups\n",
    "    .unpivot(\n",
    "        index='academic_year', \n",
    "        on=[\n",
    "            'students',\n",
    "            'us_students',\n",
    "            'undergraduate',\n",
    "            'graduate',\n",
    "            'non_degree',\n",
    "            'opt'\n",
    "        ],\n",
    "        variable_name='student_type',\n",
    "        value_name='count'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "ename": "ComputeError",
     "evalue": "found multiple elements in the same group, please specify an aggregation function",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mComputeError\u001b[0m                              Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/4l/yz8bs9_14yj6_1v4hn7dh70h0000gn/T/ipykernel_6218/1616760760.py\u001b[0m in \u001b[0;36m?\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m (\n\u001b[1;32m      2\u001b[0m     \u001b[0mlong_df_with_dups\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m     .pivot(\n\u001b[0m\u001b[1;32m      4\u001b[0m         \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'academic_year'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m         \u001b[0mvalues\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'count'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m         \u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'student_type'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Desktop/Polars-Cookbook/.venv/lib/python3.11/site-packages/polars/dataframe/frame.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self, values, index, columns, aggregate_function, maintain_order, sort_columns, separator)\u001b[0m\n\u001b[1;32m   7408\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7409\u001b[0m             \u001b[0maggregate_expr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0maggregate_function\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_pyexpr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7410\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7411\u001b[0m         return self._from_pydf(\n\u001b[0;32m-> 7412\u001b[0;31m             self._df.pivot_expr(\n\u001b[0m\u001b[1;32m   7413\u001b[0m                 \u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7414\u001b[0m                 \u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7415\u001b[0m                 \u001b[0mcolumns\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mComputeError\u001b[0m: found multiple elements in the same group, please specify an aggregation function"
     ]
    }
   ],
   "source": [
    "(\n",
    "    long_df_with_dups\n",
    "    .pivot(\n",
    "        index='academic_year', \n",
    "        values='count', \n",
    "        on='student_type'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    long_df_with_dups\n",
    "    .pivot(\n",
    "        index='academic_year', \n",
    "        values='count', \n",
    "        on='student_type',\n",
    "        aggregate_function='min'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>[1095299, 1095299]</td><td>[19828000, 19828000]</td><td>[431930, 431930]</td><td>[377943, 377943]</td><td>[62341, 62341]</td><td>[223085, 223085]</td></tr><tr><td>&quot;2019/20&quot;</td><td>[1075496]</td><td>[19720000]</td><td>[419321]</td><td>[374435]</td><td>[58201]</td><td>[223539]</td></tr><tr><td>&quot;2020/21&quot;</td><td>[914095]</td><td>[19744000]</td><td>[359787]</td><td>[329272]</td><td>[21151]</td><td>[203885]</td></tr><tr><td>&quot;2021/22&quot;</td><td>[948519]</td><td>[20327000]</td><td>[344532]</td><td>[385097]</td><td>[34131]</td><td>[184759]</td></tr><tr><td>&quot;2022/23&quot;</td><td>[1057188]</td><td>[18961280]</td><td>[347602]</td><td>[467027]</td><td>[43766]</td><td>[198793]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌──────────────┬─────────────┬─────────────┬─────────────┬─────────────┬─────────────┬─────────────┐\n",
       "│ academic_yea ┆ students    ┆ us_students ┆ undergradua ┆ graduate    ┆ non_degree  ┆ opt         │\n",
       "│ r            ┆ ---         ┆ ---         ┆ te          ┆ ---         ┆ ---         ┆ ---         │\n",
       "│ ---          ┆ list[i64]   ┆ list[i64]   ┆ ---         ┆ list[i64]   ┆ list[i64]   ┆ list[i64]   │\n",
       "│ str          ┆             ┆             ┆ list[i64]   ┆             ┆             ┆             │\n",
       "╞══════════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╡\n",
       "│ 2018/19      ┆ [1095299,   ┆ [19828000,  ┆ [431930,    ┆ [377943,    ┆ [62341,     ┆ [223085,    │\n",
       "│              ┆ 1095299]    ┆ 19828000]   ┆ 431930]     ┆ 377943]     ┆ 62341]      ┆ 223085]     │\n",
       "│ 2019/20      ┆ [1075496]   ┆ [19720000]  ┆ [419321]    ┆ [374435]    ┆ [58201]     ┆ [223539]    │\n",
       "│ 2020/21      ┆ [914095]    ┆ [19744000]  ┆ [359787]    ┆ [329272]    ┆ [21151]     ┆ [203885]    │\n",
       "│ 2021/22      ┆ [948519]    ┆ [20327000]  ┆ [344532]    ┆ [385097]    ┆ [34131]     ┆ [184759]    │\n",
       "│ 2022/23      ┆ [1057188]   ┆ [18961280]  ┆ [347602]    ┆ [467027]    ┆ [43766]     ┆ [198793]    │\n",
       "└──────────────┴─────────────┴─────────────┴─────────────┴─────────────┴─────────────┴─────────────┘"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    long_df_with_dups\n",
    "    .pivot(\n",
    "        index='academic_year', \n",
    "        values='count', \n",
    "        on='student_type',\n",
    "        aggregate_function=pl.element()\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>undergraduate</th><th>non_degree</th><th>us_students</th><th>opt</th><th>graduate</th><th>students</th></tr><tr><td>str</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td><td>list[i64]</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>[431930, 431930]</td><td>[62341, 62341]</td><td>[19828000, 19828000]</td><td>[223085, 223085]</td><td>[377943, 377943]</td><td>[1095299, 1095299]</td></tr><tr><td>&quot;2019/20&quot;</td><td>[419321]</td><td>[58201]</td><td>[19720000]</td><td>[223539]</td><td>[374435]</td><td>[1075496]</td></tr><tr><td>&quot;2020/21&quot;</td><td>[359787]</td><td>[21151]</td><td>[19744000]</td><td>[203885]</td><td>[329272]</td><td>[914095]</td></tr><tr><td>&quot;2021/22&quot;</td><td>[344532]</td><td>[34131]</td><td>[20327000]</td><td>[184759]</td><td>[385097]</td><td>[948519]</td></tr><tr><td>&quot;2022/23&quot;</td><td>[347602]</td><td>[43766]</td><td>[18961280]</td><td>[198793]</td><td>[467027]</td><td>[1057188]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌──────────────┬─────────────┬─────────────┬─────────────┬─────────────┬─────────────┬─────────────┐\n",
       "│ academic_yea ┆ undergradua ┆ non_degree  ┆ us_students ┆ opt         ┆ graduate    ┆ students    │\n",
       "│ r            ┆ te          ┆ ---         ┆ ---         ┆ ---         ┆ ---         ┆ ---         │\n",
       "│ ---          ┆ ---         ┆ list[i64]   ┆ list[i64]   ┆ list[i64]   ┆ list[i64]   ┆ list[i64]   │\n",
       "│ str          ┆ list[i64]   ┆             ┆             ┆             ┆             ┆             │\n",
       "╞══════════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╪═════════════╡\n",
       "│ 2018/19      ┆ [431930,    ┆ [62341,     ┆ [19828000,  ┆ [223085,    ┆ [377943,    ┆ [1095299,   │\n",
       "│              ┆ 431930]     ┆ 62341]      ┆ 19828000]   ┆ 223085]     ┆ 377943]     ┆ 1095299]    │\n",
       "│ 2019/20      ┆ [419321]    ┆ [58201]     ┆ [19720000]  ┆ [223539]    ┆ [374435]    ┆ [1075496]   │\n",
       "│ 2020/21      ┆ [359787]    ┆ [21151]     ┆ [19744000]  ┆ [203885]    ┆ [329272]    ┆ [914095]    │\n",
       "│ 2021/22      ┆ [344532]    ┆ [34131]     ┆ [20327000]  ┆ [184759]    ┆ [385097]    ┆ [948519]    │\n",
       "│ 2022/23      ┆ [347602]    ┆ [43766]     ┆ [18961280]  ┆ [198793]    ┆ [467027]    ┆ [1057188]   │\n",
       "└──────────────┴─────────────┴─────────────┴─────────────┴─────────────┴─────────────┴─────────────┘"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_cols = [ \n",
    "    ( \n",
    "        pl.col('count') \n",
    "        .filter(pl.col('student_type')==stu_type) \n",
    "        .alias(stu_type)\n",
    "    )\n",
    "    for stu_type in student_types\n",
    "]\n",
    "\n",
    "(\n",
    "    long_df_with_dups\n",
    "    .group_by('academic_year', maintain_order=True)\n",
    "    .agg(agg_cols)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting ready"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from polars import selectors as cs\n",
    "\n",
    "academic_df = (\n",
    "    pl.read_csv('../data/academic.csv')\n",
    "    .select(\n",
    "        pl.col('year').alias('academic_year'), \n",
    "        cs.numeric().cast(pl.Int64)\n",
    "    )\n",
    "    .filter(pl.col('academic_year').str.slice(0,4).cast(pl.Int32)>=2018)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "academic_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (16, 10)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>year</th><th>female</th><th>male</th><th>single</th><th>married</th><th>full_time</th><th>part_time</th><th>visa_f</th><th>visa_j</th><th>visa_other</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2007/08&quot;</td><td>278841</td><td>344964</td><td>543958</td><td>79847</td><td>575772</td><td>48033</td><td>552691</td><td>31814</td><td>39300</td></tr><tr><td>&quot;2008/09&quot;</td><td>304242</td><td>367374</td><td>591694</td><td>79922</td><td>613185</td><td>58431</td><td>589007</td><td>39625</td><td>42984</td></tr><tr><td>&quot;2009/10&quot;</td><td>309534</td><td>381389</td><td>615612</td><td>75311</td><td>637722</td><td>53201</td><td>612158</td><td>38692</td><td>40073</td></tr><tr><td>&quot;2010/11&quot;</td><td>322582</td><td>400695</td><td>653842</td><td>69435</td><td>669031</td><td>54246</td><td>645163</td><td>40504</td><td>37610</td></tr><tr><td>&quot;2011/12&quot;</td><td>338671</td><td>425824</td><td>690339</td><td>74156</td><td>714038</td><td>50457</td><td>688810</td><td>42047</td><td>33638</td></tr><tr><td>&quot;2012/13&quot;</td><td>363922</td><td>455722</td><td>744237</td><td>75407</td><td>769646</td><td>49998</td><td>747515</td><td>42621</td><td>29508</td></tr><tr><td>&quot;2013/14&quot;</td><td>390749</td><td>495303</td><td>806307</td><td>79745</td><td>829345</td><td>56707</td><td>804535</td><td>49619</td><td>31898</td></tr><tr><td>&quot;2014/15&quot;</td><td>426043</td><td>548883</td><td>886208</td><td>88718</td><td>912531</td><td>62395</td><td>881333</td><td>58496</td><td>35097</td></tr><tr><td>&quot;2015/16&quot;</td><td>451982</td><td>591857</td><td>950937</td><td>92902</td><td>971814</td><td>72025</td><td>957200</td><td>52192</td><td>34447</td></tr><tr><td>&quot;2016/17&quot;</td><td>470366</td><td>608456</td><td>970940</td><td>107882</td><td>1007620</td><td>71202</td><td>994674</td><td>45311</td><td>38837</td></tr><tr><td>&quot;2017/18&quot;</td><td>477329</td><td>617463</td><td>999545</td><td>95247</td><td>1028010</td><td>66782</td><td>1015967</td><td>44886</td><td>33939</td></tr><tr><td>&quot;2018/19&quot;</td><td>480836</td><td>614463</td><td>1002199</td><td>93100</td><td>1030676</td><td>64623</td><td>1018628</td><td>44907</td><td>31764</td></tr><tr><td>&quot;2019/20&quot;</td><td>477520</td><td>597976</td><td>987305</td><td>88191</td><td>1016344</td><td>59152</td><td>1000211</td><td>44095</td><td>31190</td></tr><tr><td>&quot;2020/21&quot;</td><td>407686</td><td>506409</td><td>827256</td><td>86839</td><td>843710</td><td>70385</td><td>860163</td><td>16454</td><td>37478</td></tr><tr><td>&quot;2021/22&quot;</td><td>428731</td><td>519788</td><td>861255</td><td>87264</td><td>884020</td><td>64499</td><td>884020</td><td>27507</td><td>36992</td></tr><tr><td>&quot;2022/23&quot;</td><td>473620</td><td>583568</td><td>966270</td><td>90918</td><td>986356</td><td>70832</td><td>989528</td><td>34887</td><td>32773</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (16, 10)\n",
       "┌─────────┬────────┬────────┬─────────┬───┬───────────┬─────────┬────────┬────────────┐\n",
       "│ year    ┆ female ┆ male   ┆ single  ┆ … ┆ part_time ┆ visa_f  ┆ visa_j ┆ visa_other │\n",
       "│ ---     ┆ ---    ┆ ---    ┆ ---     ┆   ┆ ---       ┆ ---     ┆ ---    ┆ ---        │\n",
       "│ str     ┆ i64    ┆ i64    ┆ i64     ┆   ┆ i64       ┆ i64     ┆ i64    ┆ i64        │\n",
       "╞═════════╪════════╪════════╪═════════╪═══╪═══════════╪═════════╪════════╪════════════╡\n",
       "│ 2007/08 ┆ 278841 ┆ 344964 ┆ 543958  ┆ … ┆ 48033     ┆ 552691  ┆ 31814  ┆ 39300      │\n",
       "│ 2008/09 ┆ 304242 ┆ 367374 ┆ 591694  ┆ … ┆ 58431     ┆ 589007  ┆ 39625  ┆ 42984      │\n",
       "│ 2009/10 ┆ 309534 ┆ 381389 ┆ 615612  ┆ … ┆ 53201     ┆ 612158  ┆ 38692  ┆ 40073      │\n",
       "│ 2010/11 ┆ 322582 ┆ 400695 ┆ 653842  ┆ … ┆ 54246     ┆ 645163  ┆ 40504  ┆ 37610      │\n",
       "│ 2011/12 ┆ 338671 ┆ 425824 ┆ 690339  ┆ … ┆ 50457     ┆ 688810  ┆ 42047  ┆ 33638      │\n",
       "│ …       ┆ …      ┆ …      ┆ …       ┆ … ┆ …         ┆ …       ┆ …      ┆ …          │\n",
       "│ 2018/19 ┆ 480836 ┆ 614463 ┆ 1002199 ┆ … ┆ 64623     ┆ 1018628 ┆ 44907  ┆ 31764      │\n",
       "│ 2019/20 ┆ 477520 ┆ 597976 ┆ 987305  ┆ … ┆ 59152     ┆ 1000211 ┆ 44095  ┆ 31190      │\n",
       "│ 2020/21 ┆ 407686 ┆ 506409 ┆ 827256  ┆ … ┆ 70385     ┆ 860163  ┆ 16454  ┆ 37478      │\n",
       "│ 2021/22 ┆ 428731 ┆ 519788 ┆ 861255  ┆ … ┆ 64499     ┆ 884020  ┆ 27507  ┆ 36992      │\n",
       "│ 2022/23 ┆ 473620 ┆ 583568 ┆ 966270  ┆ … ┆ 70832     ┆ 989528  ┆ 34887  ┆ 32773      │\n",
       "└─────────┴────────┴────────┴─────────┴───┴───────────┴─────────┴────────┴────────────┘"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "status_df = (\n",
    "    pl.read_csv('../data/status.csv')\n",
    "    .with_columns(\n",
    "        cs.float().cast(pl.Int64)\n",
    "    )\n",
    ")\n",
    "status_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>visa_f</th><th>visa_j</th><th>visa_other</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>1018628</td><td>44907</td><td>31764</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>1000211</td><td>44095</td><td>31190</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>860163</td><td>16454</td><td>37478</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>884020</td><td>27507</td><td>36992</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>989528</td><td>34887</td><td>32773</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 5)\n",
       "┌───────────────┬──────────┬─────────┬────────┬────────────┐\n",
       "│ academic_year ┆ students ┆ visa_f  ┆ visa_j ┆ visa_other │\n",
       "│ ---           ┆ ---      ┆ ---     ┆ ---    ┆ ---        │\n",
       "│ str           ┆ i64      ┆ i64     ┆ i64    ┆ i64        │\n",
       "╞═══════════════╪══════════╪═════════╪════════╪════════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 1018628 ┆ 44907  ┆ 31764      │\n",
       "│ 2019/20       ┆ 1075496  ┆ 1000211 ┆ 44095  ┆ 31190      │\n",
       "│ 2020/21       ┆ 914095   ┆ 860163  ┆ 16454  ┆ 37478      │\n",
       "│ 2021/22       ┆ 948519   ┆ 884020  ┆ 27507  ┆ 36992      │\n",
       "│ 2022/23       ┆ 1057188  ┆ 989528  ┆ 34887  ┆ 32773      │\n",
       "└───────────────┴──────────┴─────────┴────────┴────────────┘"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_df = (\n",
    "    academic_df\n",
    "    .join(\n",
    "        status_df,\n",
    "        left_on='academic_year',\n",
    "        right_on='year',\n",
    "        how='inner'\n",
    "    )\n",
    "    .select(\n",
    "        'academic_year',\n",
    "        'students',\n",
    "        cs.contains('visa')\n",
    "    )\n",
    ")\n",
    "joined_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "alignmentgroup": "True",
         "hovertemplate": "color=visa_f<br>Year=%{x}<br>Student Count=%{y}<extra></extra>",
         "legendgroup": "visa_f",
         "marker": {
          "color": "#636efa",
          "pattern": {
           "shape": ""
          }
         },
         "name": "visa_f",
         "offsetgroup": "visa_f",
         "orientation": "v",
         "showlegend": true,
         "textposition": "inside",
         "texttemplate": "%{y:.1%}",
         "type": "bar",
         "x": [
          "2018/19",
          "2019/20",
          "2020/21",
          "2021/22",
          "2022/23"
         ],
         "xaxis": "x",
         "y": [
          0.9299999360905105,
          0.9299997396550057,
          0.9409995678786122,
          0.9320003078483404,
          0.9360000302689777
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "color=visa_j<br>Year=%{x}<br>Student Count=%{y}<extra></extra>",
         "legendgroup": "visa_j",
         "marker": {
          "color": "#EF553B",
          "pattern": {
           "shape": ""
          }
         },
         "name": "visa_j",
         "offsetgroup": "visa_j",
         "orientation": "v",
         "showlegend": true,
         "textposition": "inside",
         "texttemplate": "%{y:.1%}",
         "type": "bar",
         "x": [
          "2018/19",
          "2019/20",
          "2020/21",
          "2021/22",
          "2022/23"
         ],
         "xaxis": "x",
         "y": [
          0.04099976353488865,
          0.040999687586006826,
          0.018000317253677133,
          0.02899994623196794,
          0.03299980703526714
         ],
         "yaxis": "y"
        },
        {
         "alignmentgroup": "True",
         "hovertemplate": "color=visa_other<br>Year=%{x}<br>Student Count=%{y}<extra></extra>",
         "legendgroup": "visa_other",
         "marker": {
          "color": "#00cc96",
          "pattern": {
           "shape": ""
          }
         },
         "name": "visa_other",
         "offsetgroup": "visa_other",
         "orientation": "v",
         "showlegend": true,
         "textposition": "inside",
         "texttemplate": "%{y:.1%}",
         "type": "bar",
         "x": [
          "2018/19",
          "2019/20",
          "2020/21",
          "2021/22",
          "2022/23"
         ],
         "xaxis": "x",
         "y": [
          0.02900030037460091,
          0.029000572758987483,
          0.041000114867710685,
          0.038999745919691645,
          0.031000162695755153
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "autosize": true,
        "barmode": "stack",
        "legend": {
         "title": {
          "text": "color"
         },
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "International Student Count by Visa Type"
        },
        "uniformtext": {
         "minsize": 10,
         "mode": "hide"
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Year"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "tickformat": "0%",
         "title": {
          "text": "Student Count"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import plotly.express as px\n",
    "\n",
    "viz_df = (\n",
    "    joined_df\n",
    "    .unpivot(\n",
    "        index=['academic_year', 'students'],\n",
    "        on=cs.contains('visa'),\n",
    "        variable_name='visa_type',\n",
    "        value_name='count'\n",
    "    )\n",
    "    .with_columns(\n",
    "        (pl.col('count') / pl.col('students')).alias('percent_of_total')\n",
    "    )\n",
    ")\n",
    "\n",
    "fig = px.bar(\n",
    "    x=viz_df['academic_year'], \n",
    "    y=viz_df['percent_of_total'], \n",
    "    color=viz_df['visa_type'], \n",
    "    barmode = 'stack',\n",
    "    text_auto='.1%',\n",
    "    title='International Student Count by Visa Type',\n",
    "    labels={\n",
    "        'x': 'Year',\n",
    "        'y': 'Student Count'\n",
    "    },\n",
    ")\n",
    "\n",
    "fig.update_layout(\n",
    "    autosize=True,\n",
    "    uniformtext_minsize=10, \n",
    "    uniformtext_mode='hide',\n",
    "    yaxis_tickformat = '0%'\n",
    ")\n",
    "\n",
    "fig.update_traces(textposition='inside')\n",
    "\n",
    "fig.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>year</th><th>visa_type</th><th>count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2007/08&quot;</td><td>&quot;visa_f&quot;</td><td>552691</td></tr><tr><td>&quot;2008/09&quot;</td><td>&quot;visa_f&quot;</td><td>589007</td></tr><tr><td>&quot;2009/10&quot;</td><td>&quot;visa_f&quot;</td><td>612158</td></tr><tr><td>&quot;2010/11&quot;</td><td>&quot;visa_f&quot;</td><td>645163</td></tr><tr><td>&quot;2011/12&quot;</td><td>&quot;visa_f&quot;</td><td>688810</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌─────────┬───────────┬────────┐\n",
       "│ year    ┆ visa_type ┆ count  │\n",
       "│ ---     ┆ ---       ┆ ---    │\n",
       "│ str     ┆ str       ┆ i64    │\n",
       "╞═════════╪═══════════╪════════╡\n",
       "│ 2007/08 ┆ visa_f    ┆ 552691 │\n",
       "│ 2008/09 ┆ visa_f    ┆ 589007 │\n",
       "│ 2009/10 ┆ visa_f    ┆ 612158 │\n",
       "│ 2010/11 ┆ visa_f    ┆ 645163 │\n",
       "│ 2011/12 ┆ visa_f    ┆ 688810 │\n",
       "└─────────┴───────────┴────────┘"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "status_long_df = (\n",
    "    status_df\n",
    "    .unpivot(\n",
    "        index='year',\n",
    "        on=cs.contains('visa'),\n",
    "        variable_name='visa_type',\n",
    "        value_name='count'\n",
    "    )\n",
    ")\n",
    "status_long_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (15, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>visa_type</th><th>count</th></tr><tr><td>str</td><td>i64</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>&quot;visa_f&quot;</td><td>1018628</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>&quot;visa_f&quot;</td><td>1000211</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>&quot;visa_f&quot;</td><td>860163</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>&quot;visa_f&quot;</td><td>884020</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>&quot;visa_f&quot;</td><td>989528</td></tr><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>&quot;visa_j&quot;</td><td>44907</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>&quot;visa_j&quot;</td><td>44095</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>&quot;visa_j&quot;</td><td>16454</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>&quot;visa_j&quot;</td><td>27507</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>&quot;visa_j&quot;</td><td>34887</td></tr><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>&quot;visa_other&quot;</td><td>31764</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>&quot;visa_other&quot;</td><td>31190</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>&quot;visa_other&quot;</td><td>37478</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>&quot;visa_other&quot;</td><td>36992</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>&quot;visa_other&quot;</td><td>32773</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (15, 4)\n",
       "┌───────────────┬──────────┬────────────┬─────────┐\n",
       "│ academic_year ┆ students ┆ visa_type  ┆ count   │\n",
       "│ ---           ┆ ---      ┆ ---        ┆ ---     │\n",
       "│ str           ┆ i64      ┆ str        ┆ i64     │\n",
       "╞═══════════════╪══════════╪════════════╪═════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ visa_f     ┆ 1018628 │\n",
       "│ 2019/20       ┆ 1075496  ┆ visa_f     ┆ 1000211 │\n",
       "│ 2020/21       ┆ 914095   ┆ visa_f     ┆ 860163  │\n",
       "│ 2021/22       ┆ 948519   ┆ visa_f     ┆ 884020  │\n",
       "│ 2022/23       ┆ 1057188  ┆ visa_f     ┆ 989528  │\n",
       "│ …             ┆ …        ┆ …          ┆ …       │\n",
       "│ 2018/19       ┆ 1095299  ┆ visa_other ┆ 31764   │\n",
       "│ 2019/20       ┆ 1075496  ┆ visa_other ┆ 31190   │\n",
       "│ 2020/21       ┆ 914095   ┆ visa_other ┆ 37478   │\n",
       "│ 2021/22       ┆ 948519   ┆ visa_other ┆ 36992   │\n",
       "│ 2022/23       ┆ 1057188  ┆ visa_other ┆ 32773   │\n",
       "└───────────────┴──────────┴────────────┴─────────┘"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    academic_df\n",
    "    .join(\n",
    "        status_long_df,\n",
    "        left_on='academic_year',\n",
    "        right_on='year',\n",
    "        how='inner',\n",
    "        validate='1:m'\n",
    "    )\n",
    "    .select(\n",
    "        'academic_year',\n",
    "        'students',\n",
    "        'visa_type',\n",
    "        'count'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "ename": "ComputeError",
     "evalue": "the join keys did not fulfil 1:1 validation",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mComputeError\u001b[0m                              Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[48], line 3\u001b[0m\n\u001b[1;32m      1\u001b[0m (\n\u001b[1;32m      2\u001b[0m     \u001b[43macademic_df\u001b[49m\n\u001b[0;32m----> 3\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mjoin\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m      4\u001b[0m \u001b[43m        \u001b[49m\u001b[43mstatus_long_df\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m      5\u001b[0m \u001b[43m        \u001b[49m\u001b[43mleft_on\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43macademic_year\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m      6\u001b[0m \u001b[43m        \u001b[49m\u001b[43mright_on\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43myear\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m      7\u001b[0m \u001b[43m        \u001b[49m\u001b[43mhow\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43minner\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m      8\u001b[0m \u001b[43m        \u001b[49m\u001b[43mvalidate\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m1:1\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\n\u001b[1;32m      9\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     10\u001b[0m     \u001b[38;5;241m.\u001b[39mselect(\n\u001b[1;32m     11\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124macademic_year\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[1;32m     12\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mstudents\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[1;32m     13\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mvisa_type\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[1;32m     14\u001b[0m         \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mcount\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[1;32m     15\u001b[0m     )\n\u001b[1;32m     16\u001b[0m )\n",
      "File \u001b[0;32m~/Desktop/Polars-Cookbook/.venv/lib/python3.11/site-packages/polars/dataframe/frame.py:6401\u001b[0m, in \u001b[0;36mDataFrame.join\u001b[0;34m(self, other, on, how, left_on, right_on, suffix, validate, join_nulls)\u001b[0m\n\u001b[1;32m   6386\u001b[0m     msg \u001b[38;5;241m=\u001b[39m \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mexpected `other` join table to be a DataFrame, got \u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mtype\u001b[39m(other)\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__name__\u001b[39m\u001b[38;5;132;01m!r}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   6387\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(msg)\n\u001b[1;32m   6389\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m (\n\u001b[1;32m   6390\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mlazy\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   6391\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mjoin\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   6392\u001b[0m \u001b[43m        \u001b[49m\u001b[43mother\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mother\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mlazy\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6393\u001b[0m \u001b[43m        \u001b[49m\u001b[43mleft_on\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mleft_on\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6394\u001b[0m \u001b[43m        \u001b[49m\u001b[43mright_on\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mright_on\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6395\u001b[0m \u001b[43m        \u001b[49m\u001b[43mon\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mon\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6396\u001b[0m \u001b[43m        \u001b[49m\u001b[43mhow\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mhow\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6397\u001b[0m \u001b[43m        \u001b[49m\u001b[43msuffix\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msuffix\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6398\u001b[0m \u001b[43m        \u001b[49m\u001b[43mvalidate\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mvalidate\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6399\u001b[0m \u001b[43m        \u001b[49m\u001b[43mjoin_nulls\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mjoin_nulls\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   6400\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m-> 6401\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcollect\u001b[49m\u001b[43m(\u001b[49m\u001b[43m_eager\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m)\u001b[49m\n\u001b[1;32m   6402\u001b[0m )\n",
      "File \u001b[0;32m~/Desktop/Polars-Cookbook/.venv/lib/python3.11/site-packages/polars/lazyframe/frame.py:1730\u001b[0m, in \u001b[0;36mLazyFrame.collect\u001b[0;34m(self, type_coercion, predicate_pushdown, projection_pushdown, simplify_expression, slice_pushdown, comm_subplan_elim, comm_subexpr_elim, no_optimization, streaming, background, _eager)\u001b[0m\n\u001b[1;32m   1727\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m background:\n\u001b[1;32m   1728\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m InProcessQuery(ldf\u001b[38;5;241m.\u001b[39mcollect_concurrently())\n\u001b[0;32m-> 1730\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m wrap_df(ldf\u001b[38;5;241m.\u001b[39mcollect())\n",
      "\u001b[0;31mComputeError\u001b[0m: the join keys did not fulfil 1:1 validation"
     ]
    }
   ],
   "source": [
    "(\n",
    "    academic_df\n",
    "    .join(\n",
    "        status_long_df,\n",
    "        left_on='academic_year',\n",
    "        right_on='year',\n",
    "        how='inner',\n",
    "        validate='1:1'\n",
    "    )\n",
    "    .select(\n",
    "        'academic_year',\n",
    "        'students',\n",
    "        'visa_type',\n",
    "        'count'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There is more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (3, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>int</th><th>value</th></tr><tr><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>4</td><td>30</td></tr><tr><td>5</td><td>30</td></tr><tr><td>6</td><td>30</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (3, 2)\n",
       "┌─────┬───────┐\n",
       "│ int ┆ value │\n",
       "│ --- ┆ ---   │\n",
       "│ i64 ┆ i64   │\n",
       "╞═════╪═══════╡\n",
       "│ 4   ┆ 30    │\n",
       "│ 5   ┆ 30    │\n",
       "│ 6   ┆ 30    │\n",
       "└─────┴───────┘"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = pl.DataFrame({\n",
    "    'int': [1,2,3], 'value': [10,20,30]\n",
    "}).set_sorted('int')\n",
    "\n",
    "b = pl.DataFrame({\n",
    "    'int': [4,5,6]\n",
    "}).set_sorted('int')\n",
    "\n",
    "b.join_asof(a, on='int', strategy='backward')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenating DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting ready"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "academic_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1 = academic_df.head(2)\n",
    "df2 = academic_df.slice(2, 2)\n",
    "df3 = academic_df.tail(1)\n",
    "display(df1, df2, df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pl.concat(\n",
    "    [df1, df2, df3],\n",
    "    how='vertical'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.vstack(df2).vstack(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th></tr><tr><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>914095</td></tr><tr><td>&quot;2019/20&quot;</td><td>948519</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 2)\n",
       "┌───────────────┬──────────┐\n",
       "│ academic_year ┆ students │\n",
       "│ ---           ┆ ---      │\n",
       "│ str           ┆ i64      │\n",
       "╞═══════════════╪══════════╡\n",
       "│ 2018/19       ┆ 914095   │\n",
       "│ 2019/20       ┆ 948519   │\n",
       "└───────────────┴──────────┘"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pl.concat(\n",
    "    [\n",
    "        df1.select('academic_year'), \n",
    "        df2.select('students')\n",
    "    ],\n",
    "    how='horizontal'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th></tr><tr><td>str</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>914095</td><td>18961280</td></tr><tr><td>&quot;2019/20&quot;</td><td>948519</td><td>null</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 3)\n",
       "┌───────────────┬──────────┬─────────────┐\n",
       "│ academic_year ┆ students ┆ us_students │\n",
       "│ ---           ┆ ---      ┆ ---         │\n",
       "│ str           ┆ i64      ┆ i64         │\n",
       "╞═══════════════╪══════════╪═════════════╡\n",
       "│ 2018/19       ┆ 914095   ┆ 18961280    │\n",
       "│ 2019/20       ┆ 948519   ┆ null        │\n",
       "└───────────────┴──────────┴─────────────┘"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pl.concat(\n",
    "    [\n",
    "        df1.select('academic_year'), \n",
    "        df2.select('students'),\n",
    "        df3.select('us_students')\n",
    "    ],\n",
    "    how='horizontal'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th></tr><tr><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>914095</td></tr><tr><td>&quot;2019/20&quot;</td><td>948519</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 2)\n",
       "┌───────────────┬──────────┐\n",
       "│ academic_year ┆ students │\n",
       "│ ---           ┆ ---      │\n",
       "│ str           ┆ i64      │\n",
       "╞═══════════════╪══════════╡\n",
       "│ 2018/19       ┆ 914095   │\n",
       "│ 2019/20       ┆ 948519   │\n",
       "└───────────────┴──────────┘"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    df1\n",
    "    .select('academic_year')\n",
    "    .hstack(df2.select('students'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "ename": "ShapeError",
     "evalue": "could not create a new DataFrame: series \"academic_year\" has length 2 while series \"us_students\" has length 1",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mShapeError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/4l/yz8bs9_14yj6_1v4hn7dh70h0000gn/T/ipykernel_6218/2076770988.py\u001b[0m in \u001b[0;36m?\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m (\n\u001b[1;32m      2\u001b[0m     \u001b[0mdf1\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0;34m.\u001b[0m\u001b[0mselect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'academic_year'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m     \u001b[0;34m.\u001b[0m\u001b[0mhstack\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf2\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mselect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'students'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m     \u001b[0;34m.\u001b[0m\u001b[0mhstack\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf3\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mselect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'us_students'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m )\n",
      "\u001b[0;32m~/Desktop/Polars-Cookbook/.venv/lib/python3.11/site-packages/polars/dataframe/frame.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self, columns, in_place)\u001b[0m\n\u001b[1;32m   6543\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0min_place\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6544\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhstack_mut\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_s\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0ms\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6545\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6546\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 6547\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_from_pydf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhstack\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_s\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0ms\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mShapeError\u001b[0m: could not create a new DataFrame: series \"academic_year\" has length 2 while series \"us_students\" has length 1"
     ]
    }
   ],
   "source": [
    "(\n",
    "    df1\n",
    "    .select('academic_year')\n",
    "    .hstack(df2.select('students'))\n",
    "    .hstack(df3.select('us_students'))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There is more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.extend(df2).extend(df3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th><th>us_students</th><th>undergraduate</th><th>graduate</th><th>non_degree</th><th>opt</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>&quot;2018/19&quot;</td><td>1095299</td><td>19828000</td><td>431930</td><td>377943</td><td>62341</td><td>223085</td></tr><tr><td>&quot;2019/20&quot;</td><td>1075496</td><td>19720000</td><td>419321</td><td>374435</td><td>58201</td><td>223539</td></tr><tr><td>&quot;2020/21&quot;</td><td>914095</td><td>19744000</td><td>359787</td><td>329272</td><td>21151</td><td>203885</td></tr><tr><td>&quot;2021/22&quot;</td><td>948519</td><td>20327000</td><td>344532</td><td>385097</td><td>34131</td><td>184759</td></tr><tr><td>&quot;2022/23&quot;</td><td>1057188</td><td>18961280</td><td>347602</td><td>467027</td><td>43766</td><td>198793</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       "│ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       "│ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       "│ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       "╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       "│ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       "│ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       "│ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       "│ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       "│ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       "└───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other reshaping techniques"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[shape: (1, 7)\n",
       " ┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       " │ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       " │ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       " │ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       " ╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       " │ 2018/19       ┆ 1095299  ┆ 19828000    ┆ 431930        ┆ 377943   ┆ 62341      ┆ 223085 │\n",
       " └───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘,\n",
       " shape: (1, 7)\n",
       " ┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       " │ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       " │ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       " │ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       " ╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       " │ 2019/20       ┆ 1075496  ┆ 19720000    ┆ 419321        ┆ 374435   ┆ 58201      ┆ 223539 │\n",
       " └───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘,\n",
       " shape: (1, 7)\n",
       " ┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       " │ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       " │ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       " │ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       " ╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       " │ 2020/21       ┆ 914095   ┆ 19744000    ┆ 359787        ┆ 329272   ┆ 21151      ┆ 203885 │\n",
       " └───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘,\n",
       " shape: (1, 7)\n",
       " ┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       " │ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       " │ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       " │ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       " ╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       " │ 2021/22       ┆ 948519   ┆ 20327000    ┆ 344532        ┆ 385097   ┆ 34131      ┆ 184759 │\n",
       " └───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘,\n",
       " shape: (1, 7)\n",
       " ┌───────────────┬──────────┬─────────────┬───────────────┬──────────┬────────────┬────────┐\n",
       " │ academic_year ┆ students ┆ us_students ┆ undergraduate ┆ graduate ┆ non_degree ┆ opt    │\n",
       " │ ---           ┆ ---      ┆ ---         ┆ ---           ┆ ---      ┆ ---        ┆ ---    │\n",
       " │ str           ┆ i64      ┆ i64         ┆ i64           ┆ i64      ┆ i64        ┆ i64    │\n",
       " ╞═══════════════╪══════════╪═════════════╪═══════════════╪══════════╪════════════╪════════╡\n",
       " │ 2022/23       ┆ 1057188  ┆ 18961280    ┆ 347602        ┆ 467027   ┆ 43766      ┆ 198793 │\n",
       " └───────────────┴──────────┴─────────────┴───────────────┴──────────┴────────────┴────────┘]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "academic_df.partition_by('academic_year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (7, 6)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>column</th><th>column_0</th><th>column_1</th><th>column_2</th><th>column_3</th><th>column_4</th></tr><tr><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;academic_year&quot;</td><td>&quot;2018/19&quot;</td><td>&quot;2019/20&quot;</td><td>&quot;2020/21&quot;</td><td>&quot;2021/22&quot;</td><td>&quot;2022/23&quot;</td></tr><tr><td>&quot;students&quot;</td><td>&quot;1095299&quot;</td><td>&quot;1075496&quot;</td><td>&quot;914095&quot;</td><td>&quot;948519&quot;</td><td>&quot;1057188&quot;</td></tr><tr><td>&quot;us_students&quot;</td><td>&quot;19828000&quot;</td><td>&quot;19720000&quot;</td><td>&quot;19744000&quot;</td><td>&quot;20327000&quot;</td><td>&quot;18961280&quot;</td></tr><tr><td>&quot;undergraduate&quot;</td><td>&quot;431930&quot;</td><td>&quot;419321&quot;</td><td>&quot;359787&quot;</td><td>&quot;344532&quot;</td><td>&quot;347602&quot;</td></tr><tr><td>&quot;graduate&quot;</td><td>&quot;377943&quot;</td><td>&quot;374435&quot;</td><td>&quot;329272&quot;</td><td>&quot;385097&quot;</td><td>&quot;467027&quot;</td></tr><tr><td>&quot;non_degree&quot;</td><td>&quot;62341&quot;</td><td>&quot;58201&quot;</td><td>&quot;21151&quot;</td><td>&quot;34131&quot;</td><td>&quot;43766&quot;</td></tr><tr><td>&quot;opt&quot;</td><td>&quot;223085&quot;</td><td>&quot;223539&quot;</td><td>&quot;203885&quot;</td><td>&quot;184759&quot;</td><td>&quot;198793&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (7, 6)\n",
       "┌───────────────┬──────────┬──────────┬──────────┬──────────┬──────────┐\n",
       "│ column        ┆ column_0 ┆ column_1 ┆ column_2 ┆ column_3 ┆ column_4 │\n",
       "│ ---           ┆ ---      ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ str           ┆ str      ┆ str      ┆ str      ┆ str      ┆ str      │\n",
       "╞═══════════════╪══════════╪══════════╪══════════╪══════════╪══════════╡\n",
       "│ academic_year ┆ 2018/19  ┆ 2019/20  ┆ 2020/21  ┆ 2021/22  ┆ 2022/23  │\n",
       "│ students      ┆ 1095299  ┆ 1075496  ┆ 914095   ┆ 948519   ┆ 1057188  │\n",
       "│ us_students   ┆ 19828000 ┆ 19720000 ┆ 19744000 ┆ 20327000 ┆ 18961280 │\n",
       "│ undergraduate ┆ 431930   ┆ 419321   ┆ 359787   ┆ 344532   ┆ 347602   │\n",
       "│ graduate      ┆ 377943   ┆ 374435   ┆ 329272   ┆ 385097   ┆ 467027   │\n",
       "│ non_degree    ┆ 62341    ┆ 58201    ┆ 21151    ┆ 34131    ┆ 43766    │\n",
       "│ opt           ┆ 223085   ┆ 223539   ┆ 203885   ┆ 184759   ┆ 198793   │\n",
       "└───────────────┴──────────┴──────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "academic_df.transpose(include_header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>academic_year</th><th>students</th></tr><tr><td>array[str, 5]</td><td>array[i64, 5]</td></tr></thead><tbody><tr><td>[&quot;2018/19&quot;, &quot;2019/20&quot;, … &quot;2022/23&quot;]</td><td>[1095299, 1075496, … 1057188]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 2)\n",
       "┌─────────────────────────────────┬───────────────────────────────┐\n",
       "│ academic_year                   ┆ students                      │\n",
       "│ ---                             ┆ ---                           │\n",
       "│ array[str, 5]                   ┆ array[i64, 5]                 │\n",
       "╞═════════════════════════════════╪═══════════════════════════════╡\n",
       "│ [\"2018/19\", \"2019/20\", … \"2022… ┆ [1095299, 1075496, … 1057188] │\n",
       "└─────────────────────────────────┴───────────────────────────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    academic_df\n",
    "    .select(\n",
    "        pl.col('academic_year', 'students').reshape((1, 5))\n",
    "    )      \n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
